module.exports = {
    // 获取用户消息列表
    GET_USER_MESSAGES: `
    SELECT 
      id,
      title,
      content,
      type,
      is_read,
      created_at
    FROM messages
    WHERE user_id IS NULL OR user_id = ?
    ORDER BY created_at DESC
  `,

    // 标记消息为已读
    MARK_MESSAGE_READ: `
    UPDATE messages
    SET is_read = TRUE
    WHERE id = ? AND (user_id IS NULL OR user_id = ?)
  `,

    // 获取未读消息数量
    GET_UNREAD_COUNT: `
    SELECT COUNT(*) as count
    FROM messages
    WHERE is_read = FALSE AND (user_id IS NULL OR user_id = ?)
  `,

    // 获取用户的通知列表
    GET_USER_NOTIFICATIONS: `
    SELECT 
      n.*,
      IFNULL(un.is_read, 0) AS is_read,
      un.read_at,
      u.username AS chat_partner_username
    FROM notifications n
    LEFT JOIN user_notifications un ON n.id = un.notification_id AND un.user_id = ?
    LEFT JOIN users u ON n.target_id = u.id
    WHERE n.target_type = 'all' 
      OR (n.target_type = (SELECT role_name FROM users u JOIN user_roles r ON u.role_id = r.id WHERE u.id = ?))
      OR n.target_id = ?
    ORDER BY n.created_at DESC
  `,

    // 获取聊天记录
    GET_CHAT_HISTORY: `
    SELECT 
      m.*,
      u1.username as from_username,
      u2.username as to_username
    FROM chat_messages m
    JOIN users u1 ON m.from_user_id = u1.id
    JOIN users u2 ON m.to_user_id = u2.id
    WHERE (m.from_user_id = ? AND m.to_user_id = ?)
      OR (m.from_user_id = ? AND m.to_user_id = ?)
    ORDER BY m.created_at DESC
    LIMIT ?
  `,

    // 获取在线客服列表
    GET_ONLINE_CUSTOMER_SERVICE: `
   SELECT DISTINCT
  u.id,
  u.username,
  u.real_name,
  u.avatar_url,
  COALESCE(cs.status, 'online') as status,
  COALESCE(cs.last_active, NOW()) as last_active
FROM users u
JOIN user_roles r ON u.role_id = r.id
LEFT JOIN customer_service_status cs ON u.id = cs.user_id
WHERE r.role_name = 'customer_service'
  AND u.status = 'active';
  `,

    // 创建新通知
    CREATE_NOTIFICATION: `
    INSERT INTO notifications (
      title,
      content,
      type,
      target_type,
      target_id
    ) VALUES (?, ?, ?, ?, ?)
  `,

    // 创建客服账号
    CREATE_CUSTOMER_SERVICE: `
    INSERT INTO users (
      username,           // 登录账号
      password,          // 登录密码
      role_id,
      real_name,         // 真实姓名
      service_name,      // 客服显示名称，如"客服小李"
      phone,
      email,
      status
    ) VALUES (
      ?,
      ?,
      (SELECT id FROM user_roles WHERE role_name = 'customer_service'),
      ?,
      ?,
      ?,
      ?,
      'active'
    )
  `,

    // 初始化客服状态
    INIT_CUSTOMER_SERVICE_STATUS: `
    INSERT INTO customer_service_status (
      user_id,
      status,
      last_active
    ) VALUES (?, 'online', NOW())
  `,

    // 检查客服用户名是否存在
    CHECK_CUSTOMER_SERVICE_USERNAME: `
    SELECT 1 FROM users 
    WHERE username = ?
  `,

    // 获取咨询列表
    GET_CONSULTATIONS: `
    WITH chat_users AS (
      SELECT DISTINCT
        CASE 
          WHEN c.from_user_id = ? THEN c.to_user_id
          ELSE c.from_user_id 
        END as chat_with_id
      FROM chat_messages c
      WHERE c.from_user_id = ? OR c.to_user_id = ?
    )
    SELECT 
      u.id as chat_with_id,
      u.username,
      u.avatar_url,
      cs.status as service_status,
      COALESCE(last_msg.created_at, u.created_at) as last_time,
      last_msg.content as last_message,
      COALESCE(msg_count.total, 0) as total_messages,
      COALESCE(unread.count, 0) as unread_count
    FROM chat_users cu
    JOIN users u ON cu.chat_with_id = u.id
    JOIN user_roles r ON u.role_id = r.id
    JOIN customer_service_status cs ON u.id = cs.user_id
    LEFT JOIN (
      SELECT 
        CASE 
          WHEN from_user_id = ? THEN to_user_id
          ELSE from_user_id 
        END as user_id,
        content,
        created_at
      FROM chat_messages 
      WHERE created_at = (
        SELECT MAX(created_at)
        FROM chat_messages
        WHERE from_user_id = ? OR to_user_id = ?
      )
    ) last_msg ON u.id = last_msg.user_id
    LEFT JOIN (
      SELECT 
        CASE 
          WHEN from_user_id = ? THEN to_user_id
          ELSE from_user_id 
        END as user_id,
        COUNT(*) as total
      FROM chat_messages
      WHERE from_user_id = ? OR to_user_id = ?
      GROUP BY user_id
    ) msg_count ON u.id = msg_count.user_id
    LEFT JOIN (
      SELECT 
        CASE 
          WHEN from_user_id = ? THEN to_user_id
          ELSE from_user_id 
        END as user_id,
        COUNT(*) as count
      FROM chat_messages
      WHERE is_read = 0 AND from_user_id != ?
        AND (from_user_id = ? OR to_user_id = ?)
      GROUP BY user_id
    ) unread ON u.id = unread.user_id
    WHERE r.role_name = 'customer_service'
    ORDER BY last_time DESC
  `,

    // 获取客服详细信息
    GET_CUSTOMER_SERVICE_DETAIL: `
    SELECT 
      u.id,
      u.username,
      u.real_name,
      u.avatar_url,
      u.phone,
      u.email,
      cs.status,
      cs.last_active,
      (
        SELECT COUNT(*) 
        FROM chat_messages 
        WHERE (from_user_id = u.id OR to_user_id = u.id)
      ) as total_messages,
      (
        SELECT COUNT(DISTINCT from_user_id) 
        FROM chat_messages 
        WHERE to_user_id = u.id
      ) as total_users_served
    FROM users u
    JOIN user_roles r ON u.role_id = r.id
    JOIN customer_service_status cs ON u.id = cs.user_id
    WHERE r.role_name = 'customer_service'
      AND u.id = ?
  `
}; 